﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqToSql
{
    class LTSExample
    {
        public static void OrdersInCanada()
        {
            NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext();
            using (context)
            {
                context.Log = Console.Out;
                var custAndOrders =
                    from c in context.Customers
                    join o in context.Orders on c.CustomerID equals o.CustomerID
                    where o.ShippedDate.Value.Year == 1997 && o.ShipCountry == "Canada"
                    group c by c into g
                    select g.Key;

                foreach (var customer in custAndOrders)
                {
                    Console.WriteLine(customer.ContactName);
                }
            }
        }

        public static void OrdersInCanadaSQL()
        {
            NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext();
            using (context)
            {
                var customers = context.ExecuteQuery<Customer>("select c.Address, c.City, c.CompanyName, c.ContactName, c.ContactTitle, c.Country, c.CustomerID, c.Fax, c.Phone, c.PostalCode, c.Region from Customers c " +
                    "join Orders o on c.CustomerID = o.CustomerID " +
                    "where o.ShipCountry = 'Canada' and DATEPART(Year, o.ShippedDate) = 1997 " +
                    "group by c.Address, c.City, c.CompanyName, c.ContactName, c.ContactTitle, c.Country, c.CustomerID, c.Fax, c.Phone, c.PostalCode, c.Region");
               foreach (var cust in customers)
               {
                   Console.WriteLine(cust.ContactName);
               }
            }
        }

        public static void SalesByRegion(int regionId, DateTime start, DateTime end)
        {
            NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext();
            using (context)
            {
                var sales =
                    (from od in context.Order_Details 
                    join o in context.Orders on od.OrderID equals o.OrderID
                    where o.ShippedDate.Value >= start && o.ShippedDate <= end
                        join e in context.Employees on o.EmployeeID equals e.EmployeeID
                            join et in context.EmployeeTerritories on e.EmployeeID equals et.EmployeeID
                                join t in context.Territories on et.TerritoryID equals t.TerritoryID
                                    join r in context.Regions on t.RegionID equals r.RegionID
                    where r.RegionID == regionId
                    select new { od.Product, od.Quantity, od.UnitPrice, od.Discount }).Distinct();

                foreach (var sale in sales)
                {
                    Console.WriteLine("{0} {1}", sale.Product.ProductName, sale.Quantity);
                }
            }
        }

        static void Main()
        {
            //OrdersInCanada();
            //OrdersInCanadaSQL();
            //DAO.Add(new Customer()
            //{
            //    CustomerID = "AAAZZ",
            //    CompanyName = "Home"
            //});
            //DAO.Delete(new Customer()
            //{
            //    CustomerID = "AAAZZ",
            //    CompanyName = "Home"
            //});
            //SalesByRegion(2, DateTime.Now.AddYears(-13), DateTime.Now);
            //NorthwindDataClassesDataContext contextOne = new NorthwindDataClassesDataContext();
            //NorthwindDataClassesDataContext contextTwo = new NorthwindDataClassesDataContext();
            //contextOne.Customers.First().Address = "aasdDAda";
            //contextTwo.Customers.First().Address = "daaaa be";
            //contextOne.SubmitChanges();
            //contextTwo.SubmitChanges();
            NorthwindDataClassesDataContext context = new NorthwindDataClassesDataContext(@"Server=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\NorthwindCoppy.mdf;DataBase=NorthwindCopyy; Trusted_connection=Yes");
            context.CreateDatabase();
            //context.Employees.First().GetTerritories();
        }
    }
}
